![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Interpreting SQL TraceThis section looks at some of the statistics available from SQL Trace and how to interpret them. For each SQL statement executed, SQL Trace provides the following information:
By looking at each of these parameters, you can get an idea of how your SQL statements are being processed and which statements are taking the most time. By analyzing which statements are taking the longest, you may be able to find some inefficiencies you can correct. The SQL Trace facility was enabled in a session by using this Oracle command: EXECUTE RDBMS_system.set_sql_trace_in_session(7,3,TRUE); In another session (with SID = 7 and Serial# = 3), the following SQL statement was executed: SELECT SUBSTR(dogname,1,20) "Dog Name", SUBSTR(description,1,20) "Breed", SUBSTR(owner,1,20) "Owner" FROM dogs, breeds WHERE dogs.breed = breeds.breed ORDER BY dogs.breed; The SQL Trace facility was later disabled using this Oracle command from the fist session: EXECUTE RDBMS_system.set_sql_trace_in_session(7,3,FALSE); Following the execution of the SQL statements, the trace file was translated by running TKPROF as follows: tkprof orclshad.trc trace.out sys=no explain=ed/ed In this syntax, the following are true:
TKPROF generated the output file shown in Listing 25.1. Listing 25.1 The TKPROF Output File for a Sample Trace TKPROF: Release 7.2.2.3.0 - Beta on Sun Nov 26 13:03:20 1995 Copyright Oracle Corporation 1979, 1994. All rights reserved. Trace file: orclshad.trc Sort options: default ****************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ****************************************************************************** SELECT SUBSTR(dogname,1,20) "Dog Name", SUBSTR(breed_name,1,20) "Breed", SUBSTR(owner,1,20) "Owner" FROM dogs, breeds WHERE dogs.breed = breeds.breed ORDER BY dogs.breed call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.57 2 0 4 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.08 2 2 6 25 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.65 4 2 10 25 Misses in library cache during parse: 1 Optimizer hint: CHOOSE Parsing user id: 10 (ED) Rows Execution Plan ------ -------------------------------------------------- 0 SELECT STATEMENT HINT: CHOOSE 60 MERGE JOIN 0 SORT (JOIN) 0 TABLE ACCESS (FULL) OF BREEDS 0 SORT (JOIN) 0 TABLE ACCESS (FULL) OF DOGS ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.57 2 0 4 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.08 2 2 6 25 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.65 4 2 10 25 Misses in library cache during parse: 1 1 user SQL statements in session. 8 internal SQL statements in session. 9 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: orclshad.trc Trace file compatibilty: 7.02.01 Sort options: default 1 session in tracefile. 1 user SQL statements in trace file. 8 internal SQL statements in trace file. 9 SQL statements in trace file. 6 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: ED.prof$plan_table Default table was used. Table was created. Table was dropped. 109 lines in trace file.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |